package com.util.utils;

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;

import java.io.*;
import java.util.List;

public class Excel2Html {

    public static void main(String[] args) {
        ByteArrayOutputStream bos = new ByteArrayOutputStream();
        try {
            FileInputStream fis = new FileInputStream("C:\\Users\\Stiker\\Desktop\\tktzd_f5e29961-3d47-4803-a5fb-0deae60a1932.xlsx");
            Workbook workbook = WorkbookFactory.create(fis);
            workbook.write( bos);
        } catch (FileNotFoundException e) {
            throw new RuntimeException(e);
        } catch (IOException e) {
            throw new RuntimeException(e);
        }
        try {
            System.out.println(Excel2Html.convertToHtmlTable(bos));
        } catch (IOException e) {
            e.printStackTrace();
        }
    }
    public static String convertToHtmlTable(ByteArrayOutputStream bos) throws IOException {
        Workbook workbook;
        try (ByteArrayInputStream bis = new ByteArrayInputStream(bos.toByteArray())) {
            workbook = WorkbookFactory.create(bis);
        }
        StringBuilder html = new StringBuilder();
        html.append("<table style='border-collapse: collapse; width: 100%; min-width: 800px;'>");

        for (int sheetIndex = 0; sheetIndex < workbook.getNumberOfSheets(); sheetIndex++) {
            Sheet sheet = workbook.getSheetAt(sheetIndex);
            List<CellRangeAddress> mergedRegions = sheet.getMergedRegions();

            for (Row row : sheet) {
                html.append("<tr>");

                int colCount = row.getLastCellNum();
                for (int colIdx = 0; colIdx < row.getLastCellNum(); colIdx++) {
                    Cell cell = row.getCell(colIdx, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK);

                    if (isCoveredByMerge(mergedRegions, row.getRowNum(), colIdx)) {
                        continue;
                    }

                    CellRangeAddress region = getMergedRegion(mergedRegions, row.getRowNum(), colIdx);
                    int colSpan = (region != null) ?
                            (region.getLastColumn() - region.getFirstColumn() + 1) : 1;
                    int rowSpan = (region != null) ?
                            (region.getLastRow() - region.getFirstRow() + 1) : 1;


                    // 动态计算单元格尺寸
                    String cellStyle = new StringBuilder()
                            .append("border:1px solid #000;padding:8px;")
                            .append("width:").append((100/colCount) * colSpan).append("%;")
                            .append("height:").append(rowSpan > 1 ? "auto" : "20px").append(";") // 基础行高
                            .append("text-align:center;vertical-align:middle;")
                            .toString();

                    html.append("<td style='").append(cellStyle).append("'");
                    if (rowSpan > 1) html.append(" rowspan='").append(rowSpan).append("'");
                    if (colSpan > 1) html.append(" colspan='").append(colSpan).append("'");
                    html.append(">");

                    // 图片处理逻辑
                    String cellValue = getCellValue(cell);
                    if (isImageUrl(cellValue)) {
                        html.append("<img src='").append(cellValue).append("' style='")
                                .append("max-width:").append(100 * colSpan).append("%;") // 占满合并宽度
                                .append("max-height:100%;")
                                .append("display:block;margin:auto;") // 居中显示
                                .append("'>");
                    } else {
                        html.append(cellValue);
                    }
                    html.append("</td>");
                }
                html.append("</tr>");
            }
        }
        html.append("</table>");
        return html.toString();
    }


    // 判断是否被合并单元格覆盖（保持原样）
    private static boolean isCoveredByMerge(List<CellRangeAddress> mergedRegions, int row, int column) {
        for (CellRangeAddress region : mergedRegions) {
            if (region.isInRange(row, column) &&
                    !(region.getFirstRow() == row && region.getFirstColumn() == column)) {
                return true;
            }
        }
        return false;
    }


    // 获取单元格所属合并区域（保持原样）
    private static CellRangeAddress getMergedRegion(List<CellRangeAddress> mergedRegions, int row, int column) {
        for (CellRangeAddress region : mergedRegions) {
            if (region.getFirstRow() == row && region.getFirstColumn() == column) {
                return region;
            }
        }
        return null;
    }


    // 获取单元格内容（文本格式）
    private static String getCellValue(Cell cell) {
        return new DataFormatter().formatCellValue(cell);
    }


    private static boolean isImageUrl(String value) {
        if (!value.startsWith("http")) return false;
        String lower = value.toLowerCase();
        return lower.matches("^(http|https)://.*\\.(jpg|jpeg|png|gif)(\\?.*)?$");
    }
}
